package edu.byu.isys413.mystuff;
/*
 * membershipDAO.java
 * Author : abasnet
 *
 * Created on February 15, 2007, 8:31 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

import java.util.*;
import java.sql.*;

public class membershipDAO{
    private static membershipDAO instance;
    customerDAO cusdao = null;
    private String membershipID;
    private static Cache cache;
    
    /** Creates a new instance of customerDAO */
    public membershipDAO() {
        cache = Cache.getInstance();
        cusdao = new customerDAO();
    }
    
    public static synchronized membershipDAO getInstance() {
        if (instance == null) {
            instance = new membershipDAO();
        }
        return instance;
    }
    
    public membershipBO create(){
        membershipID = Guid.generate();
        membershipBO memBO = new membershipBO(membershipID);
        cache.put(membershipID,memBO);
        return memBO;
    }
    
    /*
     *This method reads all the record from the database  and loads the Membership Business object
     * @parameter : the id of the record
     * @ returns : the all the membershipBO, loaded with the data from the database
     */
    public synchronized ArrayList<membershipBO> readALL() throws DataException {
        ArrayList<membershipBO> membs = new ArrayList();
        Connection conn = null;
        try {
            conn = ConnectionPool.getInstance().get();
            
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM MEMBERSHIP_TBL");
            
            while(rs.next()){
                membershipBO membo = new membershipBO(rs.getString("CUST_ID"));
                membs.add(membo);
            }
            
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
            }
            
            throw new DataException("Could not retrieve records", e);
        }
        
        return membs;
    }
    
    public synchronized String findMembership(String name, String email)throws DataException{
        
        String membID = "";
        String firstName = "";
        String lastName = "";
        if(name.length()!=0){
            String[] results = name.split("\\s");
            firstName = results[0];
            lastName = results[results.length-1];
        }
        
        Connection conn = null;
        try {
            conn = ConnectionPool.getInstance().get();
            
            Statement st = conn.createStatement();
            
            String sqlstring = "SELECT M.MEMBERSHIP_ID FROM MEMBERSHIP_TBL M, CUSTOMER_TBL C WHERE C.FIRST_NAME='"+firstName+"' AND C.LAST_NAME='"+lastName+"' AND C.EMAIL = '"+email+"' AND C.CUST_ID = M.CUST_ID ";
            ResultSet rs = st.executeQuery(sqlstring);
            
            while(rs.next()){
                membID = rs.getString("MEMBERSHIP_ID");
            }
            
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
            }
            
            throw new DataException("Could not retrieve records", e);
        }
        
        return membID;
    }
    
    
    public synchronized membershipBO read(String id) throws DataException {
        membershipBO membo = null;
        customerBO cusbo1 = null;
        Connection conn = null;
        String custID = "";
        try {
            conn = ConnectionPool.getInstance().get();
            read(conn, id);
            
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
            }
            throw new DataException("Could not retrieve records", e);
        }
        
        return membo;
    }
    
    public synchronized membershipBO read(Connection conn, String id){
       membershipBO membo = null;
        try{
            membo = new membershipBO(id);
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM MEMBERSHIP_TBL WHERE MEMBERSHIP_ID='"+id+"'");
            
            while(rs.next()){
                membo.setCustomerID(rs.getString("CUST_ID"));
                membo.setCrcardType(rs.getString("CREDITCARD_TYPE"));
                membo.setCrcardNumber(rs.getString("CREDITCARD_NO"));
                membo.setCrcardExpdate(rs.getString("CREDITCARD_EXP_DATE"));
                membo.setStartDate(rs.getString("START_DATE"));
                membo.setExpireDate(rs.getString("EXPIRE_DATE"));
            }
        }catch(Exception e){}
        return membo;
    }
    /////////////save////////////////////
    public synchronized boolean save(membershipBO membo) throws DataException {
        
        Connection conn = null;
        try {
            
            conn = ConnectionPool.getInstance().get();
            save(conn,membo);
            ///////////////////////////////////////////
            // call read with a connection (the other read method in this class)
            
            // release the connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
            }
            
            throw new DataException("Could not retrieve records", e);
        }
        
        return true;
    }
    
    
    public synchronized void save(Connection conn, membershipBO membo){
        try{
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM MEMBERSHIP_TBL WHERE MEMBERSHIP_ID ='"+membo.getMembershipID()+"'");
            
            String memID = "";
            while(rs.next())
                memID = rs.getString("MEMBERSHIP_ID");
            
            
            if(memID == ""){
                String queryString = "INSERT INTO MEMBERSHIP_TBL VALUES (?,?,?,?,?,?,?)";
                PreparedStatement pstmt = conn.prepareStatement(queryString);
                pstmt.setString(1,membo.getMembershipID());
                pstmt.setString(2,membo.getCustomerID());
                pstmt.setString(3,membo.getCrcardType());
                pstmt.setString(4,membo.getCrcardNumber());
                pstmt.setString(5,membo.getCrcardExpdate());
                pstmt.setString(6,membo.getStartDate());
                pstmt.setString(7,membo.getExpireDate());
                pstmt.execute();
                pstmt.close();
            } else{
                System.out.println("update--------------------------");
                String queryString = "UPDATE MEMBERSHIP_TBL SET CREDITCARD_TYPE = ?,CREDITCARD_NO = ? , CREDITCARD_EXP_DATE = ?, START_DATE = ?, EXPIRE_DATE = ? WHERE MEMBERSHIP_ID = ? ";
                PreparedStatement pstmt = conn.prepareStatement(queryString);
                pstmt.setString(1,membo.getCrcardType());
                pstmt.setString(2,membo.getCrcardNumber());
                pstmt.setString(3,membo.getCrcardExpdate());
                pstmt.setString(4,membo.getStartDate());
                pstmt.setString(5,membo.getExpireDate());
                pstmt.setString(6,membo.getMembershipID());
                pstmt.execute();
                pstmt.close();
                
            }
        }catch(Exception e){}
   }       
    /////////////delete////////////////////////
    //explain why we are not deleting
    /*
    public static void main(String[] args){
    customerDAO cusdao =  customerDAO.getInstance();
        try{
        cusdao.read("asdfasd");
        }catch(DataException e){}
    }
     */
    
}
